require('dotenv').config({ path: '../.env' }); // 指定.env文件路径
const { pool } = require('../config/db');

// 添加删除表的查询
const dropTablesQueries = [
  `DROP TABLE IF EXISTS environmental_status;`
];

const createTablesQueries = [
  `
  CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    role ENUM('user', 'admin') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  );
  `,
  `
  CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- e.g., air_conditioner, humidifier, light, sensor
    room VARCHAR(100), -- e.g., living_room, bedroom
    status BOOLEAN DEFAULT FALSE, -- true for on, false for off
    connection_status VARCHAR(50) DEFAULT 'disconnected', -- e.g., connected, disconnected, error
    device_id VARCHAR(50), -- 设备唯一标识符
    ip_address VARCHAR(50), -- 设备IP地址
    mac_address VARCHAR(50), -- 设备MAC地址
    ssid VARCHAR(100), -- 设备连接的WiFi名称
    rssi INT, -- 设备WiFi信号强度
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    user_id INT, -- Optional: if devices are user-specific
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
  );
  `,
  `
  CREATE TABLE IF NOT EXISTS device_parameters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    param_name VARCHAR(100) NOT NULL, -- e.g., temperature, humidity, mode, fan_speed, brightness
    param_value VARCHAR(255),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
  );
  `,
  `
  CREATE TABLE IF NOT EXISTS environmental_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    timestamp DATETIME NOT NULL,
    led_status VARCHAR(10),
    light FLOAT,
    temperature FLOAT,
    humidity FLOAT,
    pir VARCHAR(20),
    air_quality FLOAT,
    pressure FLOAT,
    bmp_temperature FLOAT
  );
  `,
  `
  CREATE TABLE IF NOT EXISTS scene_modes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT, -- NULL for system presets
    name VARCHAR(255) NOT NULL,
    icon VARCHAR(100), -- Icon identifier for the scene
    settings TEXT, -- JSON string containing device settings for this scene
    is_system_preset BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  );
  `,
  `
  CREATE TABLE IF NOT EXISTS user_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    theme VARCHAR(50) DEFAULT 'light',
    language VARCHAR(10) DEFAULT 'zh-CN',
    notifications_enabled BOOLEAN DEFAULT TRUE,
    comfort_temperature_min FLOAT DEFAULT 22,
    comfort_temperature_max FLOAT DEFAULT 28,
    comfort_humidity_min FLOAT DEFAULT 40,
    comfort_humidity_max FLOAT DEFAULT 65,
    acceptable_aqi_limit FLOAT DEFAULT 100,
    light_preference INT DEFAULT 3, -- e.g., 1 (dim), 2 (medium), 3 (bright)
    active_scene_id INT DEFAULT NULL, -- Foreign key to scene_modes table
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (active_scene_id) REFERENCES scene_modes(id) ON DELETE SET NULL
  );
  `,
  `
  CREATE TABLE IF NOT EXISTS notification_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    enable_all BOOLEAN DEFAULT TRUE,
    
    /* 环境提醒 */
    temperature_range BOOLEAN DEFAULT TRUE,
    humidity_range BOOLEAN DEFAULT TRUE, 
    air_quality_threshold BOOLEAN DEFAULT TRUE,
    pressure_range BOOLEAN DEFAULT FALSE,
    light_range BOOLEAN DEFAULT FALSE,
    weather BOOLEAN DEFAULT FALSE,
    
    /* 设备提醒 */
    device_status BOOLEAN DEFAULT TRUE,
    device_error BOOLEAN DEFAULT TRUE,
    auto_control BOOLEAN DEFAULT FALSE,
    pir_detection BOOLEAN DEFAULT FALSE,
    
    /* 提醒方式 */
    vibrate BOOLEAN DEFAULT TRUE,
    sound BOOLEAN DEFAULT TRUE,
    do_not_disturb BOOLEAN DEFAULT FALSE,
    do_not_disturb_start VARCHAR(5) DEFAULT '22:00',
    do_not_disturb_end VARCHAR(5) DEFAULT '07:00',
    
    /* 原有的通知设置，保留向后兼容 */
    email_notifications BOOLEAN DEFAULT TRUE,
    sms_notifications BOOLEAN DEFAULT FALSE,
    push_notifications BOOLEAN DEFAULT TRUE,
    alert_threshold_temperature FLOAT DEFAULT 30,
    alert_threshold_humidity FLOAT DEFAULT 70,
    alert_threshold_aqi FLOAT DEFAULT 150,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
  );
  `,
  `
  CREATE TABLE IF NOT EXISTS audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(255) NOT NULL, -- e.g., 'login', 'device_control', 'scene_activated'
    details TEXT, -- JSON string with more details about the action
    ip_address VARCHAR(45),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
  );
  `
];

const insertPresetScenesQueries = [
  `
  INSERT IGNORE INTO scene_modes (name, icon, settings, is_system_preset) VALUES
  ('回家模式', 'home', JSON_OBJECT(
    'air_conditioner_living_room', JSON_OBJECT('status', true, 'mode', 'auto', 'targetTemperature', 25),
    'light_living_room', JSON_OBJECT('status', true, 'brightness', 70),
    'smart_curtain_living_room', JSON_OBJECT('openPercentage', 50)
  ), TRUE),
  ('离家模式', 'away', JSON_OBJECT(
    'air_conditioner_living_room', JSON_OBJECT('status', false),
    'light_living_room', JSON_OBJECT('status', false),
    'light_bedroom', JSON_OBJECT('status', false),
    'smart_curtain_living_room', JSON_OBJECT('openPercentage', 0),
    'smart_window_living_room', JSON_OBJECT('lockStatus', true)
  ), TRUE),
  ('睡眠模式', 'sleep', JSON_OBJECT(
    'air_conditioner_bedroom', JSON_OBJECT('status', true, 'mode', 'sleep', 'targetTemperature', 23),
    'light_bedroom', JSON_OBJECT('status', false),
    'humidifier_bedroom', JSON_OBJECT('status', true, 'targetHumidity', 55),
    'smart_curtain_bedroom', JSON_OBJECT('openPercentage', 0)
  ), TRUE),
  ('观影模式', 'movie', JSON_OBJECT(
    'light_living_room', JSON_OBJECT('status', true, 'brightness', 20),
    'smart_curtain_living_room', JSON_OBJECT('openPercentage', 0),
    'air_conditioner_living_room', JSON_OBJECT('targetTemperature', 24)
  ), TRUE);
  `
];

// 为现有用户初始化通知设置
const initializeUserNotificationsQuery = `
  INSERT IGNORE INTO notification_settings (user_id, enable_all)
  SELECT id, TRUE FROM users
  WHERE id NOT IN (SELECT user_id FROM notification_settings);
`;

async function initializeDatabase() {
  let connection;
  try {
    connection = await pool.getConnection();
    console.log('成功连接到数据库。');

    console.log('开始删除旧表...');
    for (const query of dropTablesQueries) {
      try {
        await connection.query(query);
        console.log(`表 'environmental_status' 已删除。`);
      } catch (error) {
        console.error(`删除表失败:`, error.message);
        // 如果表不存在，继续执行
        if (!error.message.includes("doesn't exist")) {
          throw error;
        }
      }
    }

    console.log('开始创建表结构...');
    for (const query of createTablesQueries) {
      try {
        await connection.query(query);
        // 提取表名用于日志输出
        const tableNameMatch = query.match(/CREATE TABLE IF NOT EXISTS `?(\w+)`?/i);
        if (tableNameMatch && tableNameMatch[1]) {
            console.log(`表 '${tableNameMatch[1]}' 已检查/创建。`);
        } else {
            console.log('一个表已检查/创建。');
        }
      } catch (error) {
        // 提取表名用于错误日志输出
        const tableNameMatch = query.match(/CREATE TABLE IF NOT EXISTS `?(\w+)`?/i);
        const tableName = (tableNameMatch && tableNameMatch[1]) ? tableNameMatch[1] : '未知';
        console.error(`创建表 '${tableName}' 失败:`, error.message);
        // 如果特定错误是 'Table 'xxx' already exists'，可以忽略或仅记录
        if (!error.message.includes('already exists')) {
            throw error; // 重新抛出非"已存在"错误
        }
      }
    }
    console.log('表结构创建完成。');

    console.log('开始插入预设场景模式...');
    for (const query of insertPresetScenesQueries) {
      try {
        await connection.query(query);
        console.log('预设场景模式已插入/检查。');
      } catch (error) {
        console.error('插入预设场景模式失败:', error.message);
        // 如果特定错误是 'Duplicate entry'，可以忽略或仅记录
        if (!error.message.includes('Duplicate entry')) {
            throw error; // 重新抛出非"重复条目"错误
        }
      }
    }
    console.log('预设场景模式插入完成。');

    console.log('开始初始化用户通知设置...');
    try {
      await connection.query(initializeUserNotificationsQuery);
      console.log('用户通知设置已初始化。');
    } catch (error) {
      console.error('初始化用户通知设置失败:', error.message);
      // 如果特定错误是 'Duplicate entry'，可以忽略或仅记录
      if (!error.message.includes('Duplicate entry')) {
        throw error; // 重新抛出非"重复条目"错误
      }
    }

    console.log('数据库初始化成功完成。');

  } catch (error) {
    console.error('数据库初始化过程中发生错误:', error);
  } finally {
    if (connection) {
      connection.release();
      console.log('数据库连接已释放。');
    }
    await pool.end(); // 关闭连接池
    console.log('连接池已关闭。');
  }
}

initializeDatabase();